package db;

import beans.Shenqing;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ShenqingDao extends MySQLConnectionBean {
    private MySQLConnectionBean connection;

    public ShenqingDao() {
        connection = new MySQLConnectionBean();
    }

    // 新增方法：插入申请并返回生成的订单号
    public int insertShenqing(Shenqing shenqing) throws SQLException {
        String sql = "INSERT INTO shenqing (employeeId, cartype, departuretime, returntime, destination, usagereason, passengers, remarks, status) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        System.out.println("执行插入: " + sql);

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            statement.setInt(1, shenqing.getEmployeeId());
            statement.setString(2, shenqing.getCarType());
            statement.setTimestamp(3, new Timestamp(shenqing.getDepartureTime().getTime()));
            statement.setTimestamp(4, new Timestamp(shenqing.getReturnTime().getTime()));
            statement.setString(5, shenqing.getDestination());
            statement.setString(6, shenqing.getUsageReason());
            statement.setString(7, shenqing.getPassengers());
            statement.setString(8, shenqing.getRemarks());
            statement.setString(9, "未审核"); // 默认状态

            int affectedRows = statement.executeUpdate();

            if (affectedRows == 0) {
                throw new SQLException("插入申请失败，没有行被影响");
            }

            // 获取生成的订单号
            try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    int orderId = generatedKeys.getInt(1);
                    System.out.println("成功插入申请，生成订单号: " + orderId);
                    return orderId;
                } else {
                    throw new SQLException("插入申请失败，无法获取订单号");
                }
            }
        } catch (SQLException e) {
            System.out.println("插入申请出错: " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
    }

    // 根据订单号获取申请
    public Shenqing getShenqingById(String orderId) {
        String sql = "SELECT * FROM shenqing WHERE orderid = ?";
        System.out.println("执行订单号查询: " + sql + ", 订单号: " + orderId);

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, Integer.parseInt(orderId));
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                Shenqing shenqing = extractShenqingFromResultSet(rs);
                System.out.println("读取到记录: ID=" + shenqing.getOrderId() + ", 员工ID=" + shenqing.getEmployeeId());
                return shenqing;
            }
        } catch (SQLException e) {
            System.out.println("订单号查询出错: " + e.getMessage());
            e.printStackTrace();
        }
        return null;
    }

    // 检查申请是否存在
    public boolean shenqingExists(String orderId) {
        String sql = "SELECT COUNT(*) FROM shenqing WHERE orderid = ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, Integer.parseInt(orderId));
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            System.out.println("检查申请存在性出错: " + e.getMessage());
            e.printStackTrace();
        }
        return false;
    }

    // 新增方法：根据员工ID获取最新申请
    public Shenqing getLatestShenqingByEmployeeId(int employeeId) {
        String sql = "SELECT * FROM shenqing WHERE employeeId = ? ORDER BY orderid DESC LIMIT 1";
        System.out.println("执行最新申请查询: " + sql + ", 员工ID: " + employeeId);

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, employeeId);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                Shenqing shenqing = extractShenqingFromResultSet(rs);
                System.out.println("读取到最新申请: ID=" + shenqing.getOrderId() + ", 员工ID=" + shenqing.getEmployeeId());
                return shenqing;
            }
        } catch (SQLException e) {
            System.out.println("最新申请查询出错: " + e.getMessage());
            e.printStackTrace();
        }
        return null;
    }

    public List<Shenqing> getAllShenqing() throws SQLException {
        List<Shenqing> shenqingList = new ArrayList<>();
        String sql = "SELECT * FROM shenqing";
        System.out.println("执行查询: " + sql);

        try (Connection conn = connection.getConnection();
             PreparedStatement statement = conn.prepareStatement(sql);
             ResultSet rs = statement.executeQuery()) {

            while (rs.next()) {
                Shenqing shenqing = extractShenqingFromResultSet(rs);
                shenqingList.add(shenqing);
                System.out.println("读取到记录: ID=" + shenqing.getOrderId() + ", 员工ID=" + shenqing.getEmployeeId() + ", 状态=" + shenqing.getStatus());
            }
            System.out.println("查询到 " + shenqingList.size() + " 条记录");
        } catch (SQLException e) {
            System.out.println("查询出错: " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
        return shenqingList;
    }

    public List<Shenqing> findByStatus(String status) {
        List<Shenqing> shenqingList = new ArrayList<>();
        String sql = "SELECT * FROM shenqing WHERE status = ?";
        System.out.println("执行状态查询: " + sql + ", 状态: " + status);

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, status);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Shenqing shenqing = extractShenqingFromResultSet(rs);
                shenqingList.add(shenqing);
                System.out.println("读取到记录: ID=" + shenqing.getOrderId() + ", 员工ID=" + shenqing.getEmployeeId() + ", 状态=" + shenqing.getStatus());
            }
            System.out.println("查询到 " + shenqingList.size() + " 条记录");
        } catch (SQLException e) {
            System.out.println("状态查询出错: " + e.getMessage());
            e.printStackTrace();
        }
        return shenqingList;
    }

    public List<Shenqing> searchByOrderId(String orderId) {
        List<Shenqing> shenqingList = new ArrayList<>();
        String sql = "SELECT * FROM shenqing WHERE orderid = ?";
        System.out.println("执行订单号查询: " + sql + ", 订单号: " + orderId);

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, Integer.parseInt(orderId));
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                Shenqing shenqing = extractShenqingFromResultSet(rs);
                shenqingList.add(shenqing);
                System.out.println("读取到记录: ID=" + shenqing.getOrderId() + ", 员工ID=" + shenqing.getEmployeeId());
            }
            System.out.println("查询到 " + shenqingList.size() + " 条记录");
        } catch (SQLException e) {
            System.out.println("订单号查询出错: " + e.getMessage());
            e.printStackTrace();
        }
        return shenqingList;
    }

    private Shenqing extractShenqingFromResultSet(ResultSet rs) throws SQLException {
        Shenqing shenqing = new Shenqing();
        try {
            shenqing.setOrderId(rs.getInt("orderid"));
            shenqing.setEmployeeId(rs.getInt("employeeId"));
            shenqing.setCarType(rs.getString("cartype"));
            shenqing.setDepartureTime(rs.getTimestamp("departuretime"));
            shenqing.setReturnTime(rs.getTimestamp("returntime"));
            shenqing.setDestination(rs.getString("destination"));
            shenqing.setUsageReason(rs.getString("usagereason"));
            shenqing.setPassengers(rs.getString("passengers"));
            shenqing.setRemarks(rs.getString("remarks"));
            shenqing.setStatus(rs.getString("status"));
            System.out.println("成功提取记录: ID=" + shenqing.getOrderId() + ", 员工ID=" + shenqing.getEmployeeId());
        } catch (SQLException e) {
            System.out.println("提取记录时出错: " + e.getMessage());
            throw e;
        }
        return shenqing;
    }

    // 新增方法：更新申请状态
    public boolean updateShenqingStatus(Shenqing shenqing) throws SQLException {
        String sql = "UPDATE shenqing SET status = ? WHERE orderid = ?";
        System.out.println("执行更新状态: " + sql + ", 订单号: " + shenqing.getOrderId() + ", 新状态: " + shenqing.getStatus() );

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, shenqing.getStatus()); // 从 Shenqing 对象获取状态
            pstmt.setInt(2, shenqing.getOrderId());

            int rowsAffected = pstmt.executeUpdate();
            System.out.println("更新状态成功，影响行数: " + rowsAffected);
            return rowsAffected > 0;
        } catch (SQLException e) {
            System.out.println("更新状态出错: " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
    }
}
